import requests
import time
import hmac
import base64
import hashlib
import json
import os
import logging
from pathlib import Path
from datetime import datetime
import re
import pandas as pd

class YTCalcClient:
    # 全局缓存变量
    _global_pricing_data = {}  # 产品信息
    _global_pricing_info = {}  # 计费信息（三层嵌套：国家->线路->运费记录）
    _global_tax_rates = {}
    _global_dimension_limits = {}
    _global_current_tariff_file = None
    _global_eff_date = None
    _global_version_no = None
    _global_data_loaded = False
    
    def __init__(self):
        self.tariff_dir = Path(__file__).parent / "tariff"
        self.tariff_dir.mkdir(exist_ok=True)
        
        # 使用全局缓存数据
        self.pricing_data = self._global_pricing_data  # 产品信息
        self.pricing_info = self._global_pricing_info  # 计费信息（三层嵌套：国家->线路->运费记录）
        self.tax_rates = self._global_tax_rates
        self.dimension_limits = self._global_dimension_limits
        self.current_tariff_file = self._global_current_tariff_file
        self._eff_date = self._global_eff_date
        self._version_no = self._global_version_no
        
        # 如果数据未加载，则加载一次
        if not self._global_data_loaded:
            self.load_pricing_data()
            self._update_global_cache()
    
    def _update_global_cache(self):
        """更新全局缓存"""
        self._global_pricing_data = self.pricing_data
        self._global_pricing_info = self.pricing_info
        self._global_tax_rates = self.tax_rates
        self._global_dimension_limits = self.dimension_limits
        self._global_current_tariff_file = self.current_tariff_file
        self._global_eff_date = self._eff_date
        self._global_version_no = self._version_no
        self._global_data_loaded = True
        print("信息: 数据已缓存到全局变量")
    
    @classmethod
    def clear_global_cache(cls):
        """清空全局缓存"""
        cls._global_pricing_data = {}
        cls._global_pricing_info = {}
        cls._global_tax_rates = {}
        cls._global_dimension_limits = {}
        cls._global_current_tariff_file = None
        cls._global_eff_date = None
        cls._global_version_no = None
        cls._global_data_loaded = False
        print("信息: 全局缓存已清空")
    
    @classmethod
    def is_global_cache_loaded(cls):
        """检查全局缓存是否已加载"""
        return cls._global_data_loaded
    
    @classmethod
    def get_global_cache_info(cls):
        """获取全局缓存信息"""
        return {
            'loaded': cls._global_data_loaded,
            'pricing_data_count': len(cls._global_pricing_data),
            'pricing_info_count': len(cls._global_pricing_info),
            'tax_rates_count': len(cls._global_tax_rates),
            'dimension_limits_count': len(cls._global_dimension_limits),
            'current_file': cls._global_current_tariff_file['file_name'] if cls._global_current_tariff_file else None,
            'effective_date': cls._global_eff_date,
            'version': cls._global_version_no
        }
    
    def load_pricing_data(self):
        """加载tariff目录下的最新有效Excel报价文件"""
        try:
            # 获取所有Excel文件
            excel_files = list(self.tariff_dir.glob("*.xlsx")) + list(self.tariff_dir.glob("*.xls"))
            
            # 筛选有效的CN开头文件
            valid_files = self._filter_valid_tariff_files(excel_files)
            
            if not valid_files:
                print("警告: 未找到有效的CN开头资费文件")
                return
            
            # 选择最新的文件
            latest_file = self._select_latest_tariff_file(valid_files)
            
            if latest_file:
                self.current_tariff_file = latest_file
                # 设置私有变量
                self._eff_date = latest_file['effective_date_str']
                self._version_no = latest_file['version']
                print(f"信息: 选择最新资费文件: {latest_file['file_name']} (生效时间: {latest_file['effective_date']}, 版本: {latest_file['version']})")
                self.parse_excel_file(latest_file['file_path'])
            else:
                print("错误: 无法确定最新的资费文件")
            
        except Exception as e:
            print(f"错误: 加载报价文件失败: {str(e)}")
    
    def _filter_valid_tariff_files(self, excel_files):
        """筛选有效的CN开头资费文件"""
        valid_files = []
        
        for file_path in excel_files:
            file_name = file_path.stem  # 不包含扩展名的文件名
            
            # 检查是否符合CN开头格式
            if self._is_valid_tariff_filename(file_name):
                file_info = self._parse_tariff_filename(file_name, file_path)
                if file_info:
                    valid_files.append(file_info)
                    print(f"信息: 发现有效资费文件: {file_name}")
            else:
                print(f"调试: 跳过无效文件: {file_name}")
        
        return valid_files
    
    def _is_valid_tariff_filename(self, filename):
        """检查文件名是否符合CN开头格式"""
        # 格式: CN0934765整合渠道报价产品20250901-7809333
        # CN开头，包含两个-分割的部分
        pattern = r'^CN\d+.*-\d+.*$'
        return bool(re.match(pattern, filename))
    
    def _parse_tariff_filename(self, filename, file_path):
        """解析资费文件名，提取生效时间和版本号"""
        try:
            # 按-分割文件名
            parts = filename.split('-')
            
            if len(parts) < 2:
                print(f"警告: 文件名格式不正确: {filename}")
                return None
            
            # 第一部分: CN0934765整合渠道报价产品20250901
            # 从中提取日期部分 (假设日期在最后8位)
            first_part = parts[0]
            date_match = re.search(r'(\d{8})$', first_part)
            
            if not date_match:
                print(f"警告: 无法从文件名提取日期: {filename}")
                return None
            
            effective_date_str = date_match.group(1)
            
            # 解析日期
            try:
                effective_date = datetime.strptime(effective_date_str, '%Y%m%d')
            except ValueError:
                print(f"警告: 日期格式不正确: {effective_date_str}")
                return None
            
            # 第二部分: 版本号 (如 7809333)
            version = parts[1] if len(parts) > 1 else "unknown"
            
            # 没有第三部分了
            additional_info = ""
            
            return {
                'file_name': filename,
                'file_path': file_path,
                'effective_date': effective_date,
                'effective_date_str': effective_date_str,
                'version': version,
                'additional_info': additional_info,
                'full_name': file_path.name
            }
            
        except Exception as e:
            print(f"错误: 解析文件名失败 {filename}: {str(e)}")
            return None
    
    def _select_latest_tariff_file(self, valid_files):
        """选择最新的资费文件（生效时间最接近现在的）"""
        if not valid_files:
            return None
        
        # 按生效时间排序，选择最新的

        latest_file = max(valid_files, key=lambda x: x['effective_date'])
        
        # 记录所有文件的信息
        print("信息: 发现的有效资费文件:")
        for file_info in sorted(valid_files, key=lambda x: x['effective_date'], reverse=True):
            print(f"  - {file_info['file_name']} (生效: {file_info['effective_date_str']}, 版本: {file_info['version']})")
        
        return latest_file
    
    def parse_excel_file(self, file_path):
        """解析单个Excel文件"""
        try:
            # 读取Excel文件的所有工作表，不自动处理表头
            excel_data = pd.read_excel(file_path, sheet_name=None, header=None)


            
            
            # 获取工作表列表
            sheet_names = list(excel_data.keys())
            if not sheet_names:
                print("错误: Excel文件中没有工作表")
                return
            
            # 只解析第一个工作表 (sheet[0]) 来获取产品信息
            first_sheet_name = sheet_names[0]
            first_sheet_data = excel_data[first_sheet_name]
            
            print(f"信息: 解析第一个工作表: {first_sheet_name}")
            
            # 清理第一个工作表数据
            first_sheet_data = first_sheet_data.dropna(how='all').dropna(axis=1, how='all')
            
            # 从第一个工作表提取产品信息
            self._extract_products_from_first_sheet(first_sheet_data)
            print(f"信息: 从第一个工作表提取到 {len(valid_products)} 个有效产品")
            
            
            # 解析其他工作表，但只处理在第一个工作表中存在的产品
            for i, (sheet_name, df) in enumerate(excel_data.items()):
                if i == 0:  # 跳过第一个工作表，已经处理过了
                    continue
                
                # 检查这个工作表是否对应有效产品
                matched_product = self._find_matching_product(sheet_name, valid_products)
                if matched_product:
                    print(f"信息: 解析价格工作表: {sheet_name} -> {matched_product['product_name']} ({matched_product['shipping_code']})")
                    
                    # 清理数据
                    df = df.dropna(how='all').dropna(axis=1, how='all')
                    
                    print(f"信息: 工作表 {sheet_name} 包含 {df.shape[0]} 行 {df.shape[1]} 列数据")
                    
                    # 解析价格信息并存储到pricing_info中
                    self._parse_pricing_sheet(df, matched_product)
                    
            
            # 解析税率表
            self._parse_tax_rates_table(excel_data)
            
            # 解析尺寸限制表
            self._parse_dimension_limits_table(excel_data)
                
        except Exception as e:
            print(f"错误: 解析Excel文件 {file_path} 失败: {str(e)}")
    
    def _extract_products_from_first_sheet(self, df):
        """从第一个工作表提取产品信息，返回以product_name为主键的map结构"""
        valid_products = {}
        
        try:
            # 从第6行第2列开始读取，寻找表头
            start_row = 0  # 第6行，索引为5
            start_col = 0  # 第2列，索引为1
            
            product_name_col = None
            shipping_code_col = None
            first_category_col = None
            second_category_col = None
            header_row = None
            
            print(f"调试: 开始从第{start_row+1}行第{start_col+1}列扫描表头...")
            print(f"调试: 数据框形状: {df.shape}")
            
            # 从第6行第2列开始扫描，寻找表头
            for row_idx in range(start_row, min(start_row + 10, len(df))):  # 最多扫描10行
                print(f"调试: 扫描第{row_idx+1}行...")
                
                # 先寻找一级分类
                first_category_col = None
                for col_idx in range(start_col, len(df.columns)):
                    cell_value = str(df.iloc[row_idx, col_idx]).strip()
                    if cell_value == 'nan' or not cell_value:
                        continue
                    
                    if '一级分类' in cell_value:
                        first_category_col = col_idx
                        header_row = row_idx
                        break
                
                # 如果找到一级分类，验证后续列是否为完整的表头
                if first_category_col is not None:
                    print(f"调试: 验证第{row_idx+1}行的表头完整性...")
                    
                    # 验证表头：一级分类 -> 二级分类 -> 产品名称 -> 运输代码 -> 报价表 -> 走货属性 -> 优势推荐 -> 更新时间
                    expected_headers = ['一级分类', '二级分类', '产品名称', '运输代码', '报价表', '走货属性', '优势推荐', '更新时间']
                    found_headers = []
                    header_cols = {}
                    
                    # 从一级分类列开始，扫描后续列，寻找所有可能的表头
                    for i in range(len(expected_headers)):
                        check_col = first_category_col + i
                        if check_col < len(df.columns):
                            cell_value = str(df.iloc[row_idx, check_col]).strip()
                            # 检查这个单元格是否匹配任何期望的表头
                            matched_header = None
                            for expected_header in expected_headers:
                                if expected_header in cell_value and expected_header not in found_headers:
                                    matched_header = expected_header
                                    break
                            
                            if matched_header:
                                found_headers.append(matched_header)
                                header_cols[matched_header] = check_col
                                print(f"调试: ✓ 找到 {matched_header} 在第{check_col+1}列")
                            else:
                                print(f"调试: ✗ 第{check_col+1}列不匹配任何期望表头")
                    
                    # 如果找到了核心表头（前4个），确认这是产品工作表
                    core_headers = ['一级分类', '二级分类', '产品名称', '运输代码']
                    if all(header in found_headers for header in core_headers):
                        print(f"调试: ✓ 确认第{row_idx+1}行是完整的产品表头")
                        print(f"调试: 找到的所有列: {list(header_cols.keys())}")
                        header_row = row_idx
                        first_category_col = header_cols['一级分类']
                        second_category_col = header_cols['二级分类']
                        product_name_col = header_cols['产品名称']
                        shipping_code_col = header_cols['运输代码']
                        # 保存所有找到的列信息
                        all_header_cols = header_cols.copy()
                        break
                    else:
                        print(f"调试: ✗ 第{row_idx+1}行表头不完整，继续扫描...")
                        print(f"调试: 找到的列: {list(header_cols.keys())}")
                        first_category_col = None
            
            if not product_name_col or not shipping_code_col or header_row is None:
                print("警告: 未找到产品名称或运输代码列")
                return valid_products
            
            print(f"调试: 表头位置 - 行: {header_row+1}, 产品名称列: {product_name_col+1}, 运输代码列: {shipping_code_col+1}")
            if first_category_col is not None:
                print(f"调试: 一级分类列: {first_category_col+1}")
            if second_category_col is not None:
                print(f"调试: 二级分类列: {second_category_col+1}")
            
            # 从表头下一行开始顺序读取数据
            for row_idx in range(header_row + 1, len(df)):
                try:
                    product_name = str(df.iloc[row_idx, product_name_col]).strip()
                    shipping_code = str(df.iloc[row_idx, shipping_code_col]).strip()
                    
                    # 处理合并单元格，跳过空值
                    if product_name == 'nan' or not product_name or shipping_code == 'nan' or not shipping_code:
                        continue
                    
                    # 提取所有列的数据
                    product_data = {
                        'product_name': product_name,
                        'shipping_code': shipping_code
                    }
                    
                    # 提取分类信息
                    if first_category_col is not None:
                        first_category = str(df.iloc[row_idx, first_category_col]).strip()
                        product_data['first_category'] = first_category if first_category != 'nan' else ""
                    
                    if second_category_col is not None:
                        second_category = str(df.iloc[row_idx, second_category_col]).strip()
                        product_data['second_category'] = second_category if second_category != 'nan' else ""
                    
                    # 提取其他列的数据
                    for header_name, col_idx in all_header_cols.items():
                        if header_name not in ['一级分类', '二级分类', '产品名称', '运输代码']:
                            try:
                                cell_value = str(df.iloc[row_idx, col_idx]).strip()
                                product_data[header_name] = cell_value if cell_value != 'nan' else ""
                                print(f"调试: 提取列 {header_name}: {product_data[header_name]}")
                            except:
                                product_data[header_name] = ""
                                print(f"调试: 提取列 {header_name} 失败，设为空值")
                    
                    # 以product_name为主键存储
                    product_name = product_data.get('product_name', '')
                    if product_name:
                        valid_products[product_name] = product_data
                    
                    # 构建调试信息
                    debug_info = f"调试: 提取产品: {shipping_code} - {product_name}"
                    if 'first_category' in product_data:
                        debug_info += f" (一级分类: {product_data['first_category']}"
                    if 'second_category' in product_data:
                        debug_info += f", 二级分类: {product_data['second_category']}"
                    debug_info += ")"
                    print(debug_info)
                    
                except Exception as e:
                    print(f"调试: 读取第{row_idx+1}行数据时出错: {str(e)}")
                    continue
            
        except Exception as e:
            print(f"错误: 提取产品信息失败: {str(e)}")
        
        return valid_products
    
    def _parse_pricing_sheet(self, df, matched_product):
        """解析价格工作表的计费信息，返回三层嵌套结构：国家 -> 线路 -> 运费记录"""
        
        try:
            # 寻找表头行
            header_row = None
            country_col = None
            timeliness_col = None
            zone_col = None
            weight_col = None
            increment_col = None
            min_weight_col = None
            freight_col = None
            registration_col = None
            
            # 从第1行开始扫描，寻找表头
            for row_idx in range(min(10, len(df))):  # 最多扫描前10行
                for col_idx in range(len(df.columns)):
                    cell_value = str(df.iloc[row_idx, col_idx]).strip()
                    if cell_value == 'nan' or not cell_value:
                        continue
                    
                    # 寻找国家/地区列
                    if '国家' in cell_value or '地区' in cell_value or 'country' in cell_value.lower():
                        country_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找参考时效列
                    elif '时效' in cell_value or 'timeliness' in cell_value.lower():
                        timeliness_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找分区列
                    elif '分区' in cell_value or 'zone' in cell_value.lower():
                        zone_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找重量列
                    elif '重量' in cell_value or 'weight' in cell_value.lower():
                        weight_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找进位制列
                    elif '进位制' in cell_value or 'increment' in cell_value.lower():
                        increment_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找最低计费重列
                    elif '最低计费重' in cell_value or 'minimum' in cell_value.lower():
                        min_weight_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找运费列
                    elif '运费' in cell_value or 'freight' in cell_value.lower():
                        freight_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找挂号费列
                    elif '挂号费' in cell_value or 'registration' in cell_value.lower():
                        registration_col = col_idx
                        if header_row is None:
                            header_row = row_idx
            
            if header_row is None:
                print("警告: 未找到价格表头")
                return False
            
            
            # 从表头下一行开始读取数据，处理合并单元格
            current_country = ""
            current_timeliness = ""
            current_zone = ""
            
            for row_idx in range(header_row + 1, len(df)):
                try:
                    # 处理合并单元格：如果当前行有值就更新，否则使用之前的值
                    if country_col is not None:
                        country_value = str(df.iloc[row_idx, country_col]).strip()
                        if country_value != 'nan' and country_value:
                            current_country = country_value
                    
                    if timeliness_col is not None:
                        timeliness_value = str(df.iloc[row_idx, timeliness_col]).strip()
                        if timeliness_value != 'nan' and timeliness_value:
                            current_timeliness = timeliness_value
                    
                    if zone_col is not None:
                        zone_value = str(df.iloc[row_idx, zone_col]).strip()
                        if zone_value != 'nan' and zone_value:
                            current_zone = zone_value
                    
                    # 提取重量信息
                    weight = ""
                    if weight_col is not None:
                        weight = str(df.iloc[row_idx, weight_col]).strip()
                        if weight == 'nan':
                            weight = ""
                    
                    # 提取运费信息
                    freight = ""
                    if freight_col is not None:
                        freight = str(df.iloc[row_idx, freight_col]).strip()
                        if freight == 'nan':
                            freight = ""
                    
                    # 提取挂号费信息
                    registration = ""
                    if registration_col is not None:
                        registration = str(df.iloc[row_idx, registration_col]).strip()
                        if registration == 'nan':
                            registration = ""
                    
                    # 提取最低计费重信息
                    min_weight = ""
                    if min_weight_col is not None:
                        min_weight = str(df.iloc[row_idx, min_weight_col]).strip()
                        if min_weight == 'nan':
                            min_weight = ""
                    
                    # 提取进位制信息
                    increment = ""
                    if increment_col is not None:
                        increment = str(df.iloc[row_idx, increment_col]).strip()
                        if increment == 'nan':
                            increment = ""
                    
                    # 如果这一行有重量或运费数据，就添加到结果中
                    if weight or freight or registration:
                        pricing_record = {
                            'row': row_idx + 1,
                            'country': current_country,
                            'timeliness': current_timeliness,
                            'zone': current_zone,
                            'weight': weight,
                            'increment': increment,
                            'min_weight': min_weight,
                            'freight': freight,
                            'registration': registration,
                            'product_name': matched_product['product_name'],
                            'shipping_code': matched_product['shipping_code']
                        }
                        
                        # 按三层嵌套结构存储：国家 -> 线路 -> 运费记录
                        if current_country not in self.pricing_info:
                            self.pricing_info[current_country] = {}
                        
                        product_name = matched_product['product_name']
                        if product_name not in self.pricing_info[current_country]:
                            self.pricing_info[current_country][product_name] = []
                        
                        self.pricing_info[current_country][product_name].append(pricing_record)
                        # 注释掉详细的计费信息打印，避免输出太多
                        # print(f"调试: 提取计费信息 - 行{row_idx+1}: 国家={current_country}, 时效={current_timeliness}, 分区={current_zone}, 重量={weight}, 运费={freight}, 挂号费={registration}")
                
                except Exception as e:
                    continue
            
        except Exception as e:
            print(f"错误: 解析价格信息失败: {str(e)}")
        
        return True
    
    def _parse_tax_rates_table(self, excel_data):
        """解析欧洲国家对应税率表"""
        try:
            # 寻找税率表工作表
            tax_sheet_name = None
            for sheet_name in excel_data.keys():
                if '欧洲国家对应税率表' in sheet_name or '税率表' in sheet_name:
                    tax_sheet_name = sheet_name
                    break
            
            if not tax_sheet_name:
                print("调试: 未找到欧洲国家对应税率表")
                return
            
            print(f"调试: 找到税率表: {tax_sheet_name}")
            df = excel_data[tax_sheet_name]
            
            # 寻找表头行
            header_row = None
            country_col = None
            vat_rate_col = None
            service_fee_col = None
            
            # 从第0行第0列开始扫描，寻找表头
            for row_idx in range(len(df)):
                for col_idx in range(len(df.columns)):
                    cell_value = str(df.iloc[row_idx, col_idx]).strip()
                    if cell_value == 'nan' or not cell_value:
                        continue
                    
                    # 寻找国家列
                    if cell_value == '国家':
                        country_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找VAT税率列
                    elif cell_value == 'VAT税率':
                        vat_rate_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找服务费列
                    elif cell_value == '服务费':
                        service_fee_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                
                # 如果找到了表头，就停止扫描
                if header_row is not None:
                    break
            
            if header_row is None or country_col is None:
                print("警告: 未找到税率表表头")
                return
            
            print(f"调试: 税率表表头位置 - 行: {header_row+1}, 国家列: {country_col+1}, VAT税率列: {vat_rate_col+1 if vat_rate_col is not None else 'None'}, 服务费列: {service_fee_col+1 if service_fee_col is not None else 'None'}")
            
            # 从表头下一行开始读取数据
            for row_idx in range(header_row + 1, len(df)):
                try:
                    # 提取国家信息
                    country = str(df.iloc[row_idx, country_col]).strip()
                    if country == 'nan' or not country:
                        continue
                    
                    # 提取VAT税率
                    vat_rate = ""
                    if vat_rate_col is not None:
                        vat_rate = str(df.iloc[row_idx, vat_rate_col]).strip()
                        if vat_rate == 'nan':
                            vat_rate = ""
                    
                    # 提取服务费
                    service_fee = ""
                    if service_fee_col is not None:
                        service_fee = str(df.iloc[row_idx, service_fee_col]).strip()
                        if service_fee == 'nan':
                            service_fee = ""
                    
                    # 存储税率信息
                    if country:
                        self.tax_rates[country] = {
                            'vat_rate': vat_rate,
                            'service_fee': service_fee
                        }
                        # 注释掉详细的税率信息打印，避免输出太多
                        # print(f"调试: 提取税率信息 - 国家: {country}, VAT税率: {vat_rate}, 服务费: {service_fee}")
                
                except Exception as e:
                    continue
            
            print(f"信息: 解析到 {len(self.tax_rates)} 个国家的税率信息")
            
        except Exception as e:
            print(f"错误: 解析税率表失败: {str(e)}")

    def _parse_dimension_limits_table(self, excel_data):
        """解析包装尺寸限制表"""
        try:
            # 寻找尺寸限制表工作表
            dimension_sheet_name = None
            for sheet_name in excel_data.keys():
                if '尺寸表' in sheet_name:
                    dimension_sheet_name = sheet_name
                    break
            
            if not dimension_sheet_name:
                return
            
            df = excel_data[dimension_sheet_name]
            
            # 寻找表头行
            header_row = None
            country_col = None
            route_col = None  # 线路列
            min_size_col = None
            normal_size_col = None
            extra_charge_size_col = None
            
            # 从第0行开始扫描寻找表头
            for row_idx in range(len(df)):
                for col_idx in range(len(df.columns)):
                    cell_value = str(df.iloc[row_idx, col_idx]).strip()
                    if cell_value == 'nan' or not cell_value:
                        continue
                    
                    # 寻找国家/地区列
                    if cell_value == '国家/地区' or cell_value == '国家' or cell_value == '地区':
                        country_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找线路列
                    elif '线路' in cell_value or '产品' in cell_value or '渠道' in cell_value:
                        route_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找最小尺寸列
                    elif cell_value == '最小尺寸':
                        min_size_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找正常可发尺寸列
                    elif cell_value == '正常可发尺寸':
                        normal_size_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找加收150RMB可发尺寸列
                    elif '加收' in cell_value and '150' in cell_value and '尺寸' in cell_value:
                        extra_charge_size_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                
                # 如果找到了表头，就停止扫描
                if header_row is not None:
                    break
            
            if header_row is None or country_col is None:
                return
            
            print(f"调试: 尺寸表表头位置 - 行: {header_row+1}, 国家列: {country_col+1}, 线路列: {route_col+1 if route_col is not None else 'None'}")
            
            # 从表头下一行开始读取数据，处理合并单元格
            current_route = ""
            
            for row_idx in range(header_row + 1, len(df)):
                try:
                    # 处理合并单元格：如果当前行有值就更新，否则使用之前的值
                    if route_col is not None:
                        route_value = str(df.iloc[row_idx, route_col]).strip()
                        if route_value != 'nan' and route_value:
                            current_route = route_value
                    
                    country = str(df.iloc[row_idx, country_col]).strip()
                    if country == 'nan' or not country:
                        continue
                    
                    # 解析最小尺寸
                    min_size_text = ""
                    if min_size_col is not None:
                        min_size_text = str(df.iloc[row_idx, min_size_col]).strip()
                        if min_size_text == 'nan':
                            min_size_text = ""
                    
                    # 解析正常可发尺寸
                    normal_size_text = ""
                    if normal_size_col is not None:
                        normal_size_text = str(df.iloc[row_idx, normal_size_col]).strip()
                        if normal_size_text == 'nan':
                            normal_size_text = ""
                    
                    # 解析加收150RMB可发尺寸
                    extra_charge_size_text = ""
                    if extra_charge_size_col is not None:
                        extra_charge_size_text = str(df.iloc[row_idx, extra_charge_size_col]).strip()
                        if extra_charge_size_text == 'nan':
                            extra_charge_size_text = ""
                    
                    # 构建复合键：国家_线路
                    route_key = current_route if current_route else "默认线路"
                    composite_key = f"{country}_{route_key}"
                    
                    # 保存尺寸限制信息
                    self.dimension_limits[composite_key] = {
                        'country': country,
                        'route': route_key,
                        'min_size_text': min_size_text,
                        'normal_size_text': normal_size_text,
                        'extra_charge_size_text': extra_charge_size_text
                    }
                    
                except Exception as e:
                    continue
            
            print(f"信息: 解析到 {len(self.dimension_limits)} 个国家和线路的尺寸限制信息")
            
        except Exception as e:
            print(f"错误: 解析尺寸限制表失败: {str(e)}")
    
    def _find_matching_product(self, sheet_name, valid_products):
        """查找与工作表名称匹配的产品"""
        # 清理工作表名称，去掉空格
        clean_sheet_name = sheet_name.strip()
        
        for product_name, product_info in valid_products.items():
            # 清理产品名称，去掉空格
            clean_product_name = product_name.strip()
                         
            # 多种匹配方式
            if clean_product_name and clean_product_name in clean_sheet_name:
                return product_info
            elif clean_sheet_name and clean_sheet_name in clean_product_name:
                return product_info
            elif clean_product_name and clean_sheet_name and clean_product_name == clean_sheet_name:
                return product_info
        
        return None
    
    def _is_valid_price_sheet(self, sheet_name, valid_products):
        """检查工作表是否对应有效产品"""
        return self._find_matching_product(sheet_name, valid_products) is not None
    
    def get_eff_date(self):
        """获取当前资费生效时间"""
        return self._eff_date
    
    def get_version_no(self):
        """获取当前资费版本号"""
        return self._version_no
    
    
    
    
    def get_pricing_by_destination(self, destination_country, shipping_mode=None, cargo_type=None, 
                                 length=None, width=None, height=None, weight=None, 
                                 destination_state=None, destination_postal_code=None):
        """
        根据目的地国家和其他可选参数获取计费和算费信息
        
        Args:
            destination_country (str): 目的地国家（必选）
            shipping_mode (str, optional): 快运模式（经济、快速、标准）
            cargo_type (str, optional): 走货属性（带电、服装、化妆品等）
            length (float, optional): 长度 (cm)
            width (float, optional): 宽度 (cm)
            height (float, optional): 高度 (cm)
            weight (float, optional): 重量 (kg)
            destination_state (str, optional): 目的地州
            destination_postal_code (str, optional): 目的地邮编
            
        Returns:
            dict: 包含产品信息和计费信息的响应
                {
                    'success': bool,
                    'message': str,
                    'data': {
                        'query_params': dict,
                        'available_products': dict,  # 以product_name为主键的map
                        'products': dict,  # 过滤后的产品，以product_name为主键的map
                        'summary': dict
                    }
                }
        """
        try:
            # 参数验证
            if not destination_country:
                return {
                    'success': False,
                    'message': '目的地国家是必选参数',
                    'data': None
                }
            
            # 检查是否有产品数据
            if not self.pricing_data:
                return {
                    'success': False,
                    'message': '未找到任何产品信息',
                    'data': None
                }
            
            # 第一步：根据国家从pricing_info中获取该国家下的所有线路
            country_routes = {}
            for country in self.pricing_info.keys():
                if (destination_country == country or 
                    destination_country.strip() == country.strip() or
                    destination_country.lower() == country.lower()):
                    country_routes = self.pricing_info[country]
                    break
            
            if not country_routes:
                return {
                    'success': False,
                    'message': f'未找到目的地国家 "{destination_country}" 的线路信息',
                    'data': {
                        'query_params': {
                            'destination_country': destination_country,
                            'shipping_mode': shipping_mode,
                            'cargo_type': cargo_type,
                            'length': length,
                            'width': width,
                            'height': height,
                            'weight': weight,
                            'destination_state': destination_state,
                            'destination_postal_code': destination_postal_code
                        },
                        'available_products': {},
                        'products': {},
                        'summary': {
                            'total_available_products': 0,
                            'total_filtered_products': 0,
                            'countries_supported': []
                        }
                    }
                }
            
            # 获取该国家下所有产品的信息
            country_products = {}
            for product_name in country_routes.keys():
                # 从pricing_data中查找产品信息
                for sheet_key, data in self.pricing_data.items():
                    if 'matched_product' in data and data['matched_product'].get('product_name') == product_name:
                        country_products[product_name] = data['matched_product']
                        break
            
            if not country_products:
                return {
                    'success': False,
                    'message': f'未找到支持目的地国家 "{destination_country}" 的产品',
                    'data': {
                        'query_params': {
                            'destination_country': destination_country,
                            'shipping_mode': shipping_mode,
                            'cargo_type': cargo_type,
                            'length': length,
                            'width': width,
                            'height': height,
                            'weight': weight,
                            'destination_state': destination_state,
                            'destination_postal_code': destination_postal_code
                        },
                        'available_products': {},
                        'products': {},
                        'summary': {
                            'total_available_products': 0,
                            'total_filtered_products': 0,
                            'countries_supported': list(set())
                        }
                    }
                }
            
            # 第二步：根据其他参数进一步筛选
            filtered_products = {}
            for product_name, product_info in country_products.items():
                # 检查快运模式匹配
                if shipping_mode:
                    product_name_lower = product_info.get('product_name', '').lower()
                    if shipping_mode.lower() not in product_name_lower:
                        continue
                
                # 检查走货属性匹配
                if cargo_type:
                    product_attributes = product_info.get('走货属性', '').lower()
                    if cargo_type.lower() not in product_attributes:
                        continue
                
                filtered_products[product_name] = product_info
            
            # 如果没有其他筛选条件，返回所有支持该国家的产品
            if not shipping_mode and not cargo_type:
                filtered_products = country_products
            
            # 构建响应数据
            response_data = {
                'query_params': {
                    'destination_country': destination_country,
                    'shipping_mode': shipping_mode,
                    'cargo_type': cargo_type,
                    'length': length,
                    'width': width,
                    'height': height,
                    'weight': weight,
                    'destination_state': destination_state,
                    'destination_postal_code': destination_postal_code
                },
                'available_products': country_products,
                'products': filtered_products,
                'summary': {
                    'total_available_products': len(country_products),
                    'total_filtered_products': len(filtered_products),
                    'countries_supported': [destination_country]
                }
            }
            
            # 构建新的三层嵌套pricing_data结构
            pricing_data = {destination_country: {}}
            
            # 为每个产品添加计费信息和相关计算
            for product_name, product_info in filtered_products.items():
                # 直接从country_routes中获取该产品的运费记录
                product_pricing = country_routes.get(product_name, [])
                
                # 构建三层嵌套结构：国家 -> 线路 -> 运费信息
                pricing_data[destination_country][product_name] = product_pricing
                
                # 计算计费重量（如果提供了尺寸和重量参数）
                billing_weight = None
                volumetric_weight = None
                if length and width and height and weight:
                    try:
                        volumetric_weight = self.calculate_volumetric_weight(
                            length, width, height, destination_country, shipping_code
                        )
                        billing_weight = self.calculate_billing_weight(
                            weight, length, width, height, destination_country, shipping_code
                        )
                    except Exception as e:
                        print(f"计算重量时出错: {e}")
                
                # 获取税率信息
                tax_info = self.get_tax_rate_by_country(destination_country)
                
                # 获取尺寸限制信息
                dimension_info = self.get_dimension_limits_by_country(destination_country)
                
                # 构建产品结果
                product_result = {
                    'product_info': product_info,
                    'billing_calculation': {
                        'actual_weight': weight,
                        'volumetric_weight': volumetric_weight,
                        'billing_weight': billing_weight,
                        'dimensions': {
                            'length': length,
                            'width': width,
                            'height': height
                        }
                    },
                    'tax_info': tax_info,
                    'dimension_info': dimension_info
                }
                
                # 更新响应数据中的产品信息
                response_data['products'][product_name] = product_result
            
            # 将构建好的pricing_data添加到响应数据中
            response_data['pricing_data'] = pricing_data
            
            return {
                'success': True,
                'message': f'成功获取 {len(filtered_products)} 个产品的计费信息',
                'data': response_data
            }
            
        except Exception as e:
            return {
                'success': False,
                'message': f'获取计费信息失败: {str(e)}',
                'data': None
            }
    

    def get_dimension_limits_by_country(self, country, route=None):
        """根据国家和线路获取尺寸限制信息"""
        if route:
            # 精确匹配：国家_线路
            composite_key = f"{country}_{route}"
            return self.dimension_limits.get(composite_key, None)
        else:
            # 只按国家查找，返回第一个匹配的线路
            for key, value in self.dimension_limits.items():
                if value.get('country') == country:
                    return value
            return None

    def get_dimension_limits_by_country_and_route(self, country, route):
        """根据国家和线路精确获取尺寸限制信息"""
        composite_key = f"{country}_{route}"
        return self.dimension_limits.get(composite_key, None)

    
    
    def get_billing_weight_rule_for_country(self, shipping_code, country):
        """根据运输代码和国家获取计费重量规则"""
        # 备注规则解析已移除，返回None使用默认规则
        return None
    
    def calculate_volumetric_weight(self, length, width, height, country, shipping_code):
        """计算体积重量"""
        rule = self.get_billing_weight_rule_for_country(shipping_code, country)
        if not rule:
            # 使用默认公式
            return (length * width * height) / 8000
        
        # 使用规则中的分母
        divisor = rule.get('volumetric_divisor', 8000)
        return (length * width * height) / divisor
    
    def calculate_billing_weight(self, actual_weight, length, width, height, country, shipping_code):
        """计算计费重量"""
        rule = self.get_billing_weight_rule_for_country(shipping_code, country)
        if not rule:
            # 使用默认规则：取较大者，分母8000
            volumetric_weight = (length * width * height) / 8000
            return max(actual_weight, volumetric_weight)
        
        # 检查是否只按实际重量计费（不计抛）
        if rule.get('is_actual_weight_only', False):
            return actual_weight
        
        # 计算体积重量，使用规则中的分母
        volumetric_divisor = rule.get('volumetric_divisor', 8000)
        volumetric_weight = (length * width * height) / volumetric_divisor
        
        # 统一规则：取实际重量和体积重量的较大者
        return max(actual_weight, volumetric_weight)
    
    def check_dimension(self, country, length, width, height, route=None):
        """
        检查包裹尺寸是否符合指定国家和线路的尺寸限制
        
        Args:
            country (str): 国家名称
            length (float): 长度 (cm)
            width (float): 宽度 (cm) 
            height (float): 高度 (cm)
            route (str, optional): 线路名称，如果提供则精确匹配
            
        Returns:
            dict: 校验结果
                {
                    'allowed': bool,  # 是否允许发送
                    'status': str,    # 状态描述
                    'reason': str,    # 原因说明
                    'normal_violations': list,  # 正常尺寸违规项
                    'extra_charge_required': bool,  # 是否需要额外费用
                    'country': str,   # 国家
                    'route': str      # 线路
                }
        """
        try:
            # 获取该国家和线路的尺寸限制信息
            dim_info = self.get_dimension_limits_by_country(country, route)
            if not dim_info:
                if route:
                    return {
                        'allowed': True,
                        'status': 'no_limits',
                        'reason': f'国家 {country} 线路 {route} 没有尺寸限制，允许发送',
                        'normal_violations': [],
                        'extra_charge_required': False,
                        'country': country,
                        'route': route
                    }
                else:
                    return {
                        'allowed': True,
                        'status': 'no_limits',
                        'reason': f'国家 {country} 没有尺寸限制，允许发送',
                        'normal_violations': [],
                        'extra_charge_required': False,
                        'country': country,
                        'route': route or '默认'
                    }
            
            # 解析尺寸限制
            min_size = self._parse_dimension_text(dim_info.get('min_size_text', ''))
            normal_size = self._parse_dimension_text(dim_info.get('normal_size_text', ''))
            extra_charge_size = self._parse_dimension_text(dim_info.get('extra_charge_size_text', ''))
            
            # 获取国家和线路信息
            actual_country = dim_info.get('country', country)
            actual_route = dim_info.get('route', route or '默认')
            
            # 第一步：检查是否小于最小尺寸
            if min_size and self._is_smaller_than_minimum(length, width, height, min_size):
                return {
                    'allowed': False,
                    'status': 'too_small',
                    'reason': '包裹尺寸小于最小尺寸要求，不允许发送',
                    'normal_violations': [],
                    'extra_charge_required': False,
                    'country': actual_country,
                    'route': actual_route
                }
            
            # 第二步：检查正常发送尺寸
            normal_violations = []
            if normal_size:
                normal_violations = self._check_dimension_violations(length, width, height, normal_size)
            
            # 如果正常尺寸检查通过
            if not normal_violations:
                return {
                    'allowed': True,
                    'status': 'normal',
                    'reason': '包裹尺寸符合正常发送要求',
                    'normal_violations': [],
                    'extra_charge_required': False,
                    'country': actual_country,
                    'route': actual_route
                }
            
            # 第三步：检查超大尺寸
            if extra_charge_size:
                extra_violations = self._check_dimension_violations(length, width, height, extra_charge_size)
                
                # 如果超大尺寸也不满足
                if extra_violations:
                    return {
                        'allowed': False,
                        'status': 'exceeds_all_limits',
                        'reason': '包裹尺寸不满足正常发送和超大尺寸规则，不允许发送',
                        'normal_violations': normal_violations,
                        'extra_charge_required': False,
                        'country': actual_country,
                        'route': actual_route
                    }
                else:
                    # 超大尺寸满足，但需要额外费用
                    return {
                        'allowed': True,
                        'status': 'extra_charge',
                        'reason': f'常规规格不能发送（{", ".join(normal_violations)}），但通过增加费用可以超大尺寸发送',
                        'normal_violations': normal_violations,
                        'extra_charge_required': True,
                        'country': actual_country,
                        'route': actual_route
                    }
            else:
                # 没有超大尺寸选项
                return {
                    'allowed': False,
                    'status': 'exceeds_normal',
                    'reason': f'包裹尺寸超过正常发送要求（{", ".join(normal_violations)}），不允许发送',
                    'normal_violations': normal_violations,
                    'extra_charge_required': False,
                    'country': actual_country,
                    'route': actual_route
                }
                
        except Exception as e:
            return {
                'allowed': False,
                'status': 'error',
                'reason': f'尺寸校验过程中发生错误: {str(e)}',
                'normal_violations': [],
                'extra_charge_required': False,
                'country': country,
                'route': route or '未知'
            }
    
    def _parse_dimension_text(self, dimension_text):
        """
        解析尺寸文本，提取尺寸限制信息
        
        Args:
            dimension_text (str): 尺寸文本，如 "10*15cm", "最长边≤60cm", "L+W+H≤90cm"
            
        Returns:
            dict: 解析后的尺寸限制信息
                {
                    'type': str,  # 'lwh' | 'max_side' | 'sum_three' | 'unknown'
                    'length': float,  # 长度限制
                    'width': float,   # 宽度限制  
                    'height': float,  # 高度限制
                    'max_single_side': float,  # 最长边限制
                    'max_three_sides': float,  # 三边和限制
                    'raw_text': str   # 原始文本
                }
        """
        if not dimension_text or dimension_text == 'nan':
            return None
            
        dimension_text = str(dimension_text).strip()
        
        # 模式1: L*W*H格式，如 "10*15*20cm", "L<100*W<80*H<60cm"
        lwh_pattern = r'(\d+(?:\.\d+)?)\s*\*\s*(\d+(?:\.\d+)?)\s*\*\s*(\d+(?:\.\d+)?)\s*cm'
        match = re.search(lwh_pattern, dimension_text)
        if match:
            return {
                'type': 'lwh',
                'length': float(match.group(1)),
                'width': float(match.group(2)),
                'height': float(match.group(3)),
                'max_single_side': None,
                'max_three_sides': None,
                'raw_text': dimension_text
            }
        
        # 模式2: 最长边限制，如 "最长边≤60cm", "单边不超60cm"
        max_side_pattern = r'(?:最长边|单边不超|最长边不超).*?≤?\s*(\d+(?:\.\d+)?)\s*cm'
        match = re.search(max_side_pattern, dimension_text)
        if match:
            return {
                'type': 'max_side',
                'length': None,
                'width': None,
                'height': None,
                'max_single_side': float(match.group(1)),
                'max_three_sides': None,
                'raw_text': dimension_text
            }
        
        # 模式3: 三边和限制，如 "L+W+H≤90cm", "长+宽+高≤90cm", "L+W+H<90cm"
        sum_three_pattern = r'(?:L\+W\+H|长\+宽\+高|三边和).*?[≤<]\s*(\d+(?:\.\d+)?)\s*cm'
        match = re.search(sum_three_pattern, dimension_text)
        if match:
            return {
                'type': 'sum_three',
                'length': None,
                'width': None,
                'height': None,
                'max_single_side': None,
                'max_three_sides': float(match.group(1)),
                'raw_text': dimension_text
            }
        
        # 模式4: 简单数字+cm，如 "60cm" (假设为最长边限制)
        simple_pattern = r'(\d+(?:\.\d+)?)\s*cm'
        match = re.search(simple_pattern, dimension_text)
        if match:
            return {
                'type': 'max_side',
                'length': None,
                'width': None,
                'height': None,
                'max_single_side': float(match.group(1)),
                'max_three_sides': None,
                'raw_text': dimension_text
            }
        
        # 无法解析
        return {
            'type': 'unknown',
            'length': None,
            'width': None,
            'height': None,
            'max_single_side': None,
            'max_three_sides': None,
            'raw_text': dimension_text
        }
    
    def _is_smaller_than_minimum(self, length, width, height, min_size):
        """检查是否小于最小尺寸"""
        if not min_size or min_size['type'] == 'unknown':
            return False
            
        if min_size['type'] == 'lwh':
            return (length < min_size['length'] and 
                   width < min_size['width'] and 
                   height < min_size['height'])
        elif min_size['type'] == 'max_side':
            max_side = max(length, width, height)
            return max_side < min_size['max_single_side']
        elif min_size['type'] == 'sum_three':
            sum_three = length + width + height
            return sum_three < min_size['max_three_sides']
        
        return False
    
    def _check_dimension_violations(self, length, width, height, size_limit):
        """检查尺寸违规项"""
        violations = []
        
        if not size_limit or size_limit['type'] == 'unknown':
            return violations
            
        if size_limit['type'] == 'lwh':
            if length > size_limit['length']:
                violations.append(f"长度{length}cm超过限制{size_limit['length']}cm")
            if width > size_limit['width']:
                violations.append(f"宽度{width}cm超过限制{size_limit['width']}cm")
            if height > size_limit['height']:
                violations.append(f"高度{height}cm超过限制{size_limit['height']}cm")
                
        elif size_limit['type'] == 'max_side':
            max_side = max(length, width, height)
            if max_side > size_limit['max_single_side']:
                violations.append(f"最长边{max_side}cm超过限制{size_limit['max_single_side']}cm")
                
        elif size_limit['type'] == 'sum_three':
            sum_three = length + width + height
            if sum_three > size_limit['max_three_sides']:
                violations.append(f"三边和{sum_three}cm超过限制{size_limit['max_three_sides']}cm")
        
        return violations
    

    def dump_objects(self):
        """
        直接dump self.x对象属性到同名文件
        这样在另一个类中可以直接用pickle加载使用
        """
        import pickle
        import json
        
        try:
            # 确保tariff目录存在
            self.tariff_dir.mkdir(exist_ok=True)
            
            # 1. 保存产品信息
            print("正在dump产品信息...")
            pricing_data_path = self.tariff_dir / 'pricing_data.pkl'
            with open(pricing_data_path, 'wb') as f:
                pickle.dump(self.pricing_data, f)
            
            # 2. 保存计费信息
            print("正在dump计费信息...")
            pricing_info_path = self.tariff_dir / 'pricing_info.pkl'
            with open(pricing_info_path, 'wb') as f:
                pickle.dump(self.pricing_info, f)
            
            # 3. 保存计费信息为JSON格式（用于查看结构）
            print("正在dump计费信息JSON...")
            pricing_info_json_path = self.tariff_dir / 'pricing_info.json'
            with open(pricing_info_json_path, 'w', encoding='utf-8') as f:
                # 只保存前2个国家的数据，避免文件过大
                limited_pricing_info = {}
                count = 0
                for country, routes in self.pricing_info.items():
                    if count >= 2:  # 只保存前2个国家
                        break
                    limited_pricing_info[country] = {}
                    for product_name, records in routes.items():
                        # 只保存前3条记录，避免文件过大
                        limited_pricing_info[country][product_name] = records[:3]
                    count += 1
                
                json.dump(limited_pricing_info, f, ensure_ascii=False, indent=2)
            
            # 4. 保存税率信息
            print("正在dump税率信息...")
            tax_rates_path = self.tariff_dir / 'tax_rates.pkl'
            with open(tax_rates_path, 'wb') as f:
                pickle.dump(self.tax_rates, f)
            
            # 5. 保存税率信息为JSON格式
            print("正在dump税率信息JSON...")
            tax_rates_json_path = self.tariff_dir / 'tax_rates.json'
            with open(tax_rates_json_path, 'w', encoding='utf-8') as f:
                json.dump(self.tax_rates, f, ensure_ascii=False, indent=2)
            
            # 6. 保存尺寸限制信息
            print("正在dump尺寸限制信息...")
            dimension_limits_path = self.tariff_dir / 'dimension_limits.pkl'
            with open(dimension_limits_path, 'wb') as f:
                pickle.dump(self.dimension_limits, f)
            
            # 7. 保存尺寸限制信息为JSON格式
            print("正在dump尺寸限制信息JSON...")
            dimension_limits_json_path = self.tariff_dir / 'dimension_limits.json'
            with open(dimension_limits_json_path, 'w', encoding='utf-8') as f:
                json.dump(self.dimension_limits, f, ensure_ascii=False, indent=2)
            
            print("Dump完成！生成文件:")
            print(f"  - {pricing_data_path} (产品信息)")
            print(f"  - {pricing_info_path} (计费信息)") 
            print(f"  - {pricing_info_json_path} (计费信息JSON - 查看结构)")
            print(f"  - {tax_rates_path} (税率信息)")
            print(f"  - {tax_rates_json_path} (税率信息JSON)")
            print(f"  - {dimension_limits_path} (尺寸限制信息)")
            print(f"  - {dimension_limits_json_path} (尺寸限制信息JSON)")
            
        except Exception as e:
            print(f"Dump失败: {str(e)}")
            import traceback
            traceback.print_exc()


def main():
    """本地测试主方法"""
    print("=== 云途物流Excel解析测试 ===")
    
    try:
        # 创建客户端实例
        client = YTCalcClient()
        
        # 获取当前资费文件信息
        print(f"\n当前资费文件信息:")
        print(f"  文件名: {client.current_tariff_file['file_name'] if client.current_tariff_file else 'None'}")
        print(f"  生效日期: {client.get_eff_date()}")
        print(f"  版本号: {client.get_version_no()}")
        
        # 显示基本统计信息
        print(f"\n数据统计:")
        print(f"  工作表数量: {len(client.pricing_data)}")
        print(f"  税率信息国家数: {len(client.tax_rates)}")
        print(f"  尺寸限制国家数: {len(client.dimension_limits)}")
        print(f"  计费信息国家数: {len(client.pricing_info)}")
        
        # 计算总产品数
        total_products = 0
        for sheet_key, data in client.pricing_data.items():
            if 'matched_product' in data:
                total_products += 1
            elif 'valid_products' in data:
                total_products += len(data['valid_products'])
        print(f"  产品总数: {total_products}")
        
        # 计算总计费记录数
        total_pricing_records = 0
        for country, routes in client.pricing_info.items():
            for product_name, records in routes.items():
                total_pricing_records += len(records)
        print(f"  计费记录总数: {total_pricing_records}")
        
        # 显示第一个工作表的原始数据（前3行）
        if client.pricing_data:
            first_sheet_key = list(client.pricing_data.keys())[0]
            first_sheet_data = client.pricing_data[first_sheet_key]
            print(f"\n第一个工作表原始数据（前3行）:")
            for i, record in enumerate(first_sheet_data['data'][:3]):
                print(f"  行 {i+1}: {record}")
            print()
        
        # 演示计费查询功能
        print(f"\n计费查询功能演示:")
        test_countries = ["德国", "美国", "英国", "法国", "日本"]
        for country in test_countries:
            if country in client.pricing_info:
                routes = client.pricing_info[country]
                print(f"  {country}: 找到 {len(routes)} 个线路")
                for product_name, records in list(routes.items())[:2]:  # 只显示前2个线路
                    print(f"    线路 {product_name}: {len(records)} 条计费记录")
            else:
                print(f"  {country}: 未找到计费信息")
        
        # 演示get_pricing_by_destination方法
        print(f"\nget_pricing_by_destination方法演示:")
        result = client.get_pricing_by_destination("德国")
        if result['success']:
            print(f"  德国查询成功: 找到 {len(result['data']['products'])} 个产品")
            print(f"  可用产品数: {len(result['data']['available_products'])}")
        else:
            print(f"  德国查询失败: {result['message']}")
        
        # 导出对象到pkl文件
        print(f"\n导出对象到pkl文件:")
        client.dump_objects()
        
        print(f"\n=== 测试完成 ===")
        
    except Exception as e:
        print(f"测试失败: {str(e)}")
        import traceback
        traceback.print_exc()


if __name__ == "__main__":
    main()
   